package com.pioneer.customer;

import java.lang.Integer;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.ResultSet;
import java.util.Vector;
import java.util.Enumeration;

import javax.ejb.EntityBean;
import javax.ejb.EntityContext;

import javax.ejb.CreateException;
import javax.ejb.FinderException;
import javax.ejb.EJBException;

import com.pioneer.util.DBUtil;
import com.pioneer.util.DBException;

public class CustomerBean
implements EntityBean, CustomerInterface
{
	private int cus_id;
	private String name;
	private double balance;
	private double credit_limit;
	private EntityContext context;

	public Integer ejbCreate(String name)
	throws CreateException
	{		
		this.name=name;
		this.balance=0;
		int maxid=0;
		this.credit_limit=0;
		Connection dbConnection=null;
		Statement stat=null;
		ResultSet rs=null;
		String sqlString="";
		try
		{
			dbConnection = DBUtil.getDBConnection();
			stat=dbConnection.createStatement();
			rs=stat.executeQuery("SELECT MAX(c01_cus_id) maxid from c01_customer");
			if (rs.next())
			{
				maxid=rs.getInt("maxid")+1;
			}
			sqlString="INSERT INTO c01_customer (c01_cus_id,c01_name,c01_balance,c01_credit_limit) "+
												"values ("+maxid+",'"+name+"',"+balance+","+credit_limit+")";
			//sqlString="INSERT INTO  supplier (suppid,name,status) "+
			//"VALUES (99,'"+name+"','OK')";
			int resultCount=stat.executeUpdate(sqlString);
			//System.out.println("Inserting into the database"); 
			return new Integer(maxid);
		}
		catch (DBException e)
		{
			throw new CreateException("Couldn't establish the connection");
		}
		catch (SQLException e)
		{
			throw new CreateException("Couldn't do the insert because of "+sqlString+
			" error code is "+e.getErrorCode()+" sql state is "+e.getSQLState());
		}
		finally
		{
			try
			{
				if (rs!=null) rs.close();
				if (stat!=null) stat.close();
				if (dbConnection!=null) dbConnection.close();
			}
			catch (SQLException e)
			{
				throw new CreateException(e.getMessage());
			}
		}
	}

	public void ejbPostCreate(String name)	{}
	public void ejbActivate() {}
	public void ejbPassivate() {}
	
	public void ejbLoad()
	throws EJBException
	{		
		this.cus_id=((Integer)context.getPrimaryKey()).intValue();
		Connection dbConnection=null;
		Statement stat=null;
		ResultSet rs=null;
		String sqlString="";
		try
		{
			dbConnection = DBUtil.getDBConnection();
			stat=dbConnection.createStatement();
			sqlString="SELECT * FROM c01_customer WHERE c01_cus_id='"+this.cus_id+"'";
			rs=stat.executeQuery(sqlString);
			if (rs.next())
			{
				this.name=rs.getString("c01_name");
				this.balance=rs.getDouble("c01_balance");
				this.credit_limit=rs.getDouble("c01_credit_limit");
			}
			else
			{
				throw new EJBException("No matching customer with ID of "+this.cus_id);
			}
			//System.out.println("Querying the database");
		}
		catch (DBException e)
		{
			throw new EJBException("Couldn't establish the connection");
		}
		catch (SQLException e)
		{
			throw new EJBException("Couldn't do the refresh because of "+sqlString+
			" error code is "+e.getErrorCode()+" sql state is "+e.getSQLState());
		}
		finally
		{
			try
			{
				if (rs!=null) rs.close();
				if (stat!=null) stat.close();
				if (dbConnection!=null) dbConnection.close();
			}
			catch (SQLException e)
			{
				throw new EJBException(e.getMessage());
			}
		}
	}

	public void ejbStore()
	throws EJBException
	{
		this.cus_id=((Integer)context.getPrimaryKey()).intValue();
		Connection dbConnection=null;
		Statement stat=null;
		ResultSet rs=null;
		String sqlString="";
		try
		{			
			dbConnection = DBUtil.getDBConnection();
			stat=dbConnection.createStatement();
			sqlString="UPDATE c01_customer set c01_name='"+this.name+
								"',c01_balance='"+this.balance+"',c01_credit_limit='"+
								this.credit_limit+"' WHERE c01_cus_id='"+
								this.cus_id+"'";
			int resultCount=stat.executeUpdate(sqlString);
			//System.out.println("Updating the database");
		}
		catch (DBException e)
		{
			throw new EJBException("Couldn't establish the connection because of "+e.toString());
		}
		catch (SQLException e)
		{
			throw new EJBException("Couldn't do the update because of "+sqlString+
			" error code is "+e.getErrorCode()+" sql state is "+e.getSQLState());
		}
		finally
		{
			try
			{
				if (rs!=null) rs.close();
				if (stat!=null) stat.close();
				if (dbConnection!=null) dbConnection.close();
			}
			catch (SQLException e)
			{
				throw new EJBException(e.getMessage());
			}
		}
	}

	public void ejbRemove()
	throws EJBException
	{
		this.cus_id=((Integer)context.getPrimaryKey()).intValue();
		Connection dbConnection=null;
		Statement stat=null;
		ResultSet rs=null;
		String sqlString="";
		try
		{
			dbConnection = DBUtil.getDBConnection();
			stat=dbConnection.createStatement();
			//sqlString="delete from supplier WHERE suppid = '"+this.cus_id+"'";			
			sqlString="DELETE FROM c01_customer WHERE c01_cus_id='"+this.cus_id+"'";
			int resultCount=stat.executeUpdate(sqlString);			
			//System.out.println("Removing from the database"); 
		}
		catch (DBException e)
		{
			throw new EJBException("Couldn't establish the connection");
		}
		catch (SQLException e)
		{
			throw new EJBException("Couldn't do the delete because of "+sqlString+
			" error code is "+e.getErrorCode()+" sql state is "+e.getSQLState());
		}
		finally
		{
			try
			{
				if (rs!=null) rs.close();
				if (stat!=null) stat.close();
				if (dbConnection!=null) dbConnection.close();
			}
			catch (SQLException e)
			{
				throw new EJBException(e.getMessage());
			}
		}
	}
	
	public Integer ejbFindByPrimaryKey(Integer key)
	throws FinderException
	{
	
		int searchkey=key.intValue();
		Connection dbConnection=null;
		Statement stat=null;
		ResultSet rs=null;
		String sqlString="";
		try
		{
			dbConnection = DBUtil.getDBConnection();
			stat=dbConnection.createStatement();
			sqlString="SELECT * FROM c01_customer WHERE c01_cus_id='"+searchkey+"'";
			//sqlString="SELECT * FROM supplier WHERE suppid='"+searchkey+"'";
			rs=stat.executeQuery(sqlString);
			if (!rs.next())
			{
				throw new FinderException("No matching customer id");
			}
			else
			{
				return new Integer(rs.getInt("c01_cus_id"));
				//return new Integer(rs.getInt("suppid"));
			}
		}
		catch (DBException e)
		{
			throw new FinderException(e.getMessage()+"\nCouldn't establish the connection");
		}
		catch (SQLException e)
		{
			throw new FinderException("Couldn't do the refresh because of "+sqlString+
			" error code is "+e.getErrorCode()+" sql state is "+e.getSQLState());
		}
		finally
		{
			try
			{
				if (rs!=null) rs.close();
				if (stat!=null) stat.close();
				if (dbConnection!=null) dbConnection.close();
			}
			catch (SQLException e)
			{
				throw new FinderException(e.getMessage());
			}
		}
	}	
	
	public ContactModel[] getContactsByType(String cont_type)
	{
		this.cus_id=((Integer)context.getPrimaryKey()).intValue();
		Connection dbConnection=null;
		Statement stat=null;
		ResultSet rs=null;
		Vector list=new Vector();
		ContactModel[] contactModel=null;
		String sqlString="";
		try
		{
			dbConnection = DBUtil.getDBConnection();
			stat=dbConnection.createStatement();
			sqlString="SELECT *  FROM c02_contact WHERE c02_cus_id='"+this.cus_id+
			"' and c02_cont_type ='"+cont_type+"'";
			rs=stat.executeQuery(sqlString);
			//System.out.println("Executed contacts query");
			while (rs.next())
			{
				list.addElement(new ContactModel(rs.getInt("c02_cus_id"),
				rs.getString("c02_cont_type"), rs.getInt("c02_cont_seq"),
				rs.getString("c02_address"), rs.getString("c02_city"),
				rs.getString("c02_state"), rs.getString("c02_zip"),
				rs.getString("c02_phone"), rs.getString("c02_fax")));
			}
			contactModel=new ContactModel[list.size()];
			for (int count=0;count<contactModel.length;count++)
			{
				contactModel[count]=(ContactModel)list.get(count);
			}
		}
		catch (DBException e)
		{
			//throw new FinderException("Couldn't establish the connection");
		}
		catch (SQLException e)
		{
			throw new EJBException("Couldn't do the refresh because of "+sqlString+
			" error code is "+e.getErrorCode()+" sql state is "+e.getSQLState());
		}
		finally
		{
			try
			{
				if (rs!=null) rs.close();
				if (stat!=null) stat.close();
				if (dbConnection!=null) dbConnection.close();
			}
			catch (SQLException e)
			{
				//throw new FinderException(e.getMessage());
			}
		}
		return contactModel;
	}
	
	public void addContact(String cont_type, String address, String city, String state, String zip,
	String phone, String fax)
	{
		this.cus_id=((Integer)context.getPrimaryKey()).intValue();
		int maxid=0;
		Connection dbConnection=null;
		Statement stat=null;
		ResultSet rs=null;
		String sqlString="";
		try
		{
			dbConnection = DBUtil.getDBConnection();
			stat=dbConnection.createStatement();
			rs=stat.executeQuery("SELECT MAX(c02_cont_seq) maxid from c02_contact "+
			"where c02_cus_id="+this.cus_id+" and c02_cont_type='"+cont_type+"'");
			if (rs.next())
			{
				maxid=rs.getInt("maxid")+1;
			}
			
			sqlString="INSERT INTO c02_contact (c02_cus_id, c02_cont_type, c02_cont_seq,"+
			"c02_address,c02_city,c02_state,c02_zip,c02_phone,c02_fax) values ("+this.cus_id+
			",'"+cont_type+"',"+maxid+",'"+address+"','"+city+"','"+state+"','"+zip+"','"+phone+
			"','"+fax+"')";			
			int resultCount=stat.executeUpdate(sqlString);
		}
		catch (DBException e)
		{
			//throw new FinderException("Couldn't establish the connection");
		}
		catch (SQLException e)
		{
			throw new EJBException("Couldn't do the add of contact because of "+sqlString+
			" error code is "+e.getErrorCode()+" sql state is "+e.getSQLState());
		}
		finally
		{
			try
			{
				if (rs!=null) rs.close();
				if (stat!=null) stat.close();
				if (dbConnection!=null) dbConnection.close();
			}
			catch (SQLException e)
			{
				//throw new FinderException(e.getMessage());
			}
		}			
	}
	
	public void removeContact(String cont_type, int cont_seq)
	{
		this.cus_id=((Integer)context.getPrimaryKey()).intValue();
		Connection dbConnection=null;
		Statement stat=null;
		ResultSet rs=null;
		String sqlString="";
		try
		{
			dbConnection = DBUtil.getDBConnection();
			stat=dbConnection.createStatement();
			sqlString="DELETE FROM c02_contact WHERE c02_cus_id="+this.cus_id+
			" and c02_cont_type='"+cont_type+"' and c02_cont_seq="+cont_seq;
			int resultCount=stat.executeUpdate(sqlString);			
		}
		catch (DBException e)
		{
			throw new EJBException("Couldn't establish the connection");
		}
		catch (SQLException e)
		{
			throw new EJBException("Couldn't do the delete because of "+sqlString+
			" error code is "+e.getErrorCode()+" sql state is "+e.getSQLState());
		}
		finally
		{
			try
			{
				if (rs!=null) rs.close();
				if (stat!=null) stat.close();
				if (dbConnection!=null) dbConnection.close();
			}
			catch (SQLException e)
			{
				throw new EJBException(e.getMessage());
			}
		}	
	}
	
	public void updateContact(String cont_type, int cont_seq, String address, String city, String state, String zip,
	String phone, String fax)
	{
		this.cus_id=((Integer)context.getPrimaryKey()).intValue();
		Connection dbConnection=null;
		Statement stat=null;
		ResultSet rs=null;
		String sqlString="";
		try
		{			
			dbConnection = DBUtil.getDBConnection();
			stat=dbConnection.createStatement();
			sqlString="UPDATE c02_contact set "+
			"c02_address='"+address+"',"+
			"c02_city='"+city+"',"+
			"c02_state='"+state+"',"+
			"c02_zip='"+zip+"',"+
			"c02_phone='"+phone+"',"+
			"c02_fax='"+fax+"' WHERE c02_cus_id="+this.cus_id+
			" and c02_cont_type='"+cont_type+"' and c02_cont_seq="+cont_seq;
			int resultCount=stat.executeUpdate(sqlString);
			//System.out.println("Updating the database");
		}
		catch (DBException e)
		{
			throw new EJBException("Couldn't establish the connection because of "+e.toString());
		}
		catch (SQLException e)
		{
			throw new EJBException("Couldn't do the update because of "+sqlString+
			" error code is "+e.getErrorCode()+" sql state is "+e.getSQLState());
		}
		finally
		{
			try
			{
				if (rs!=null) rs.close();
				if (stat!=null) stat.close();
				if (dbConnection!=null) dbConnection.close();
			}
			catch (SQLException e)
			{
				throw new EJBException(e.getMessage());
			}
		}	
	}

	public void setEntityContext(EntityContext newContext)
	{
		context=newContext;
	}

	public void unsetEntityContext()
	{
		context=null;
	}

	public String getName()
	{
		return this.name;
	}
	
	public void setName(String pName)
	{
		this.name=pName;
	}
	
	public double getBalance()
	{
		return this.balance;
	}
	
	public void setBalance(double pBalance)
	{
		this.balance=pBalance;
	}
	
	public double getCreditLimit()
	{
		return this.credit_limit;
	}
	
	public void setCreditLimit(double credit_limit)
	{
		this.credit_limit=credit_limit;
	}
}